{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "initial_id",
   "metadata": {
    "collapsed": true,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:57.915824300Z",
     "start_time": "2024-09-29T09:07:57.873824900Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "   学号   姓名  语文成绩  数学成绩  英语成绩   总计\n",
      "0   1  张三丰    88    92    85  265\n",
      "1   2  李四喜    90    80    93  263\n",
      "2   3  王五强    75    95    80  250\n",
      "3   4  赵六顺    82    88    91  261\n",
      "4   5  孙七乐    93    90    94  277\n",
      "5   6  周八美    78    76    87  241\n",
      "6   7  吴九明    85    85    88  258\n",
      "7   8  郑十全    91    92    89  272\n",
      "8   9  陈一飞    77    89    82  248\n",
      "9  10  郭二亮    83    87    92  262\n"
     ]
    }
   ],
   "source": [
    "# 读取二维表\n",
    "df = pd.read_excel(io='./scores.xlsx', sheet_name='Sheet1', engine='openpyxl')\n",
    "print(type(df)) # <class 'pandas.core.frame.DataFrame'>\n",
    "print(df.head(10)) # 显示前10行数据"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:57.945367300Z",
     "start_time": "2024-09-29T09:07:57.894841300Z"
    }
   },
   "id": "5bda939caa4112dc"
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------- 基础信息 ----------\n",
      "     id name  chinese  math  english  total\n",
      "0  1001  李铁牛       53    44       76  189.0\n",
      "1  1003  王二柱       61    99       88    NaN\n",
      "     id name  chinese  math  english  total\n",
      "0  1001  李铁牛       53    44       76  189.0\n",
      "1  1003  王二柱       61    99       88    NaN\n",
      "     id name  chinese  math  english  total\n",
      "0  1001  李铁牛       53    44       76  189.0\n",
      "1  1003  王二柱       61    99       88    NaN\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 6 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   id       2 non-null      int64  \n",
      " 1   name     2 non-null      object \n",
      " 2   chinese  2 non-null      int64  \n",
      " 3   math     2 non-null      int64  \n",
      " 4   english  2 non-null      int64  \n",
      " 5   total    1 non-null      float64\n",
      "dtypes: float64(1), int64(4), object(1)\n",
      "memory usage: 224.0+ bytes\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# 表格数据方式1\n",
    "data = {\n",
    "    'id': [1001, 1002],\n",
    "    'name': ['李铁牛', '王二柱'],\n",
    "    'chinese': [53, 61],\n",
    "    'math': [44, 99],\n",
    "    'english': [76, 88],\n",
    "    'total': [189, 277]\n",
    "}\n",
    "# 表格数据方式2\n",
    "data = [\n",
    "    {\n",
    "        'id': 1001,\n",
    "        'name': '李铁牛',\n",
    "        'chinese': 53,\n",
    "        'math': 44,\n",
    "        'english': 76,\n",
    "        'total': 189\n",
    "    },\n",
    "    {\n",
    "        'id': 1003,\n",
    "        'name': '王二柱',\n",
    "        'chinese': 61,\n",
    "        'math': 99,\n",
    "        'english': 88,\n",
    "        'total': None,\n",
    "    }\n",
    "]\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 基础信息\n",
    "print('---------- 基础信息 ----------')\n",
    "print(df.head(10)) # 查看前10行数据，默认为5行\n",
    "print(df.tail()) # 后几行数据，默认是后 5 行\n",
    "print(df) # 显示全部数据\n",
    "print(df.info()) # 显示数据信息"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:57.977795500Z",
     "start_time": "2024-09-29T09:07:57.925973100Z"
    }
   },
   "id": "7282e447928ffb8a"
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------- 索引 ----------\n",
      "0    李铁牛\n",
      "1    王二柱\n",
      "Name: name, dtype: object\n",
      "0    李铁牛\n",
      "1    王二柱\n",
      "Name: name, dtype: object\n",
      "0    李铁牛\n",
      "1    王二柱\n",
      "Name: name, dtype: object\n"
     ]
    }
   ],
   "source": [
    "# 索引\n",
    "print('---------- 索引 ----------')\n",
    "print(df['name'])\n",
    "print(df.name)\n",
    "print(df.loc[0:1, 'name']) # 0:1表示第0行到第1行，取得到1，name表示列名"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:57.978795200Z",
     "start_time": "2024-09-29T09:07:57.952265100Z"
    }
   },
   "id": "ebd9ee86cce6d8ca"
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------- 缺失值处理 ----------\n",
      "     id name  chinese  math  english  total\n",
      "0  1001  李铁牛       53    44       76  189.0\n",
      "     id name  chinese  math  english  total\n",
      "0  1001  李铁牛       53    44       76  189.0\n",
      "1  1003  王二柱       61    99       88    0.0\n"
     ]
    }
   ],
   "source": [
    "# 缺失值处理\n",
    "print('---------- 缺失值处理 ----------')\n",
    "df1 = df.dropna() # 删除有缺失值的行\n",
    "print(df1)\n",
    "df.fillna(value=0, inplace=True) # 填充缺失值为0\n",
    "print(df)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:57.993413Z",
     "start_time": "2024-09-29T09:07:57.968786200Z"
    }
   },
   "id": "2ffdb27130b46790"
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------- 类型转换 ----------\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2 entries, 0 to 1\n",
      "Data columns (total 6 columns):\n",
      " #   Column   Non-Null Count  Dtype  \n",
      "---  ------   --------------  -----  \n",
      " 0   id       2 non-null      int64  \n",
      " 1   name     2 non-null      object \n",
      " 2   chinese  2 non-null      int64  \n",
      " 3   math     2 non-null      float64\n",
      " 4   english  2 non-null      int64  \n",
      " 5   total    2 non-null      float64\n",
      "dtypes: float64(2), int64(3), object(1)\n",
      "memory usage: 224.0+ bytes\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "# 类型转换\n",
    "print('---------- 类型转换 ----------')\n",
    "df['math'] = df['math'].astype(float) # 也可以使用numpy的数据类型\n",
    "print(df.info())"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:58.046980200Z",
     "start_time": "2024-09-29T09:07:57.988901700Z"
    }
   },
   "id": "ea3156cc55ad6dd7"
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "---------- 选择和过滤 ----------\n",
      "     id name  chinese  math  english  total\n",
      "1  1003  王二柱       61  99.0       88    0.0\n"
     ]
    }
   ],
   "source": [
    "# 条件筛选\n",
    "print('---------- 选择和过滤 ----------')\n",
    "print(df['math'] > 90)\n",
    "print('----------------------')\n",
    "print(df[df['math'] > 90])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:08:40.227004700Z",
     "start_time": "2024-09-29T09:08:40.212865Z"
    }
   },
   "id": "f91d8306a708b8f8"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [
    "# 写入Excel\n",
    "df.columns = ['学号', '姓名', '语文', '数学', '英语', '合计']\n",
    "print(df.info())\n",
    "df.to_excel('pandas-demo.xlsx', sheet_name='Sheet1', index=False, engine='openpyxl')"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "start_time": "2024-09-29T09:07:58.058690600Z"
    }
   },
   "id": "609a57138c88207b"
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "outputs": [],
   "source": [],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-09-29T09:07:58.060690900Z",
     "start_time": "2024-09-29T09:07:58.060690900Z"
    }
   },
   "id": "5e7731ed0ed13611"
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
